package cn.eeepay.framework.dao.workOrder;


import cn.eeepay.framework.model.workOrder.TeamInfoVo;
import cn.eeepay.framework.model.workOrder.WorkCensusAgentInfo;
import cn.eeepay.framework.model.workOrder.WorkCensusCount;
import cn.eeepay.framework.model.workOrder.WorkCensusFilter;
import cn.eeepay.framework.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

public interface WorkCensusReadDao {


    /**
     * 投诉总量统计
     * @param info
     * @return
     */
    @SelectProvider(type= WorkCensusReadDao.SqlProvider.class,method="countTeamWork")
    @ResultType(Map.class)
    List<Map<String,Object>> countTeamWork(@Param("info") WorkCensusFilter info);

    /**
     * 投诉总量统计-汇总
     * @param info
     * @return
     */
    @SelectProvider(type= WorkCensusReadDao.SqlProvider.class,method="countTeamWorkCount")
    @ResultType(WorkCensusCount.class)
    WorkCensusCount countTeamWorkCount(@Param("info") WorkCensusFilter info);

    /**
     * 投诉总量统计-导出
     * @param info
     * @return
     */
    @SelectProvider(type= WorkCensusReadDao.SqlProvider.class,method="countTeamWork")
    @ResultType(Map.class)
    List<Map<String,Object>> exportTeamWork(@Param("info") WorkCensusFilter info);

    /**
     * 下级代理商被投诉排名
     * @param info
     * @return
     */
    @SelectProvider(type= WorkCensusReadDao.SqlProvider.class,method="countAgentWork")
    @ResultType(WorkCensusAgentInfo.class)
    List<WorkCensusAgentInfo> countAgentWork(@Param("info") WorkCensusFilter info);

    /**
     * 下级代理商被投诉排名-汇总
     * @param info
     * @return
     */
    @SelectProvider(type= WorkCensusReadDao.SqlProvider.class,method="countAgentWorkCount")
    @ResultType(WorkCensusCount.class)
    WorkCensusCount countAgentWorkCount(@Param("info") WorkCensusFilter info);


    /**
     * 投诉总量统计-导出
     * @param info
     * @return
     */
    @SelectProvider(type= WorkCensusReadDao.SqlProvider.class,method="countAgentWork")
    @ResultType(WorkCensusAgentInfo.class)
    List<WorkCensusAgentInfo> exportAgentWork(@Param("info") WorkCensusFilter info);

    /**
     * 一级代理商被投诉排名
     * @param info
     * @return
     */
    @SelectProvider(type= WorkCensusReadDao.SqlProvider.class,method="countAgentWork2")
    @ResultType(WorkCensusAgentInfo.class)
    List<WorkCensusAgentInfo> countAgentWork2(@Param("info") WorkCensusFilter info);

    /**
     * 一级代理商被投诉排名-汇总
     * @param info
     * @return
     */
    @SelectProvider(type= WorkCensusReadDao.SqlProvider.class,method="countAgentWorkCount2")
    @ResultType(WorkCensusCount.class)
    WorkCensusCount countAgentWorkCount2(@Param("info") WorkCensusFilter info);

    /**
     * 一级投诉总量统计-导出
     * @param info
     * @return
     */
    @SelectProvider(type= WorkCensusReadDao.SqlProvider.class,method="countAgentWork2")
    @ResultType(WorkCensusAgentInfo.class)
    List<WorkCensusAgentInfo> exportAgentWork2(@Param("info") WorkCensusFilter info);

    class SqlProvider{

        /**
         * 投诉总量统计
         * @param param
         * @return
         */
        public String countTeamWork(Map<String,Object> param){
            WorkCensusFilter info = (WorkCensusFilter) param.get("info");
            StringBuffer sb=new StringBuffer();
            sb.append("select ");
            sb.append("  sd.day_date AS dayDate, ");
            //按主组织统计
            if(info.getTeamList()!=null&&info.getTeamList().size()>0) {
                for (TeamInfoVo item : info.getTeamList()) {
                    sb.append("  IFNULL(t1.team"+item.getTeamId().replace("-","_")+" ,0) AS team"+item.getTeamId()+", ");
                }
            }
            //按子组织统计
            if(info.getTeamEntryList()!=null&&info.getTeamEntryList().size()>0) {
                for (TeamInfoVo item : info.getTeamEntryList()) {
                    sb.append("  IFNULL(t1.team"+item.getTeamId().replace("-","_")+" ,0) AS team"+item.getTeamId().replace("-","_")+", ");
                }
            }
            if(StringUtils.isNotBlank(info.getTeamStr()) || StringUtils.isNotBlank(info.getTeamEntryStr())){
                sb.append("  IFNULL(t1.count ,0) AS count, ");
            }
            sb.append("  IFNULL(t2.count ,0) AS agentCount, ");
            //针对3月31号这种减一月=2月28情况，增加时间后2位匹配
            //时间用+ 会出现2条6月30数据。
            //用减法会对应 5月31对应 4月30情况，去除掉
            sb.append("  IF(right(t1.create_time,2)=right(t3.create_time,2),IF(t3.count is null,'-',CONCAT(round((t1.count-t3.count)/t3.count*100,2),'%')),'-') AS linkRelativeRatio ");

            //sd
            sb.append(" FROM sys_date sd ");

            // t1
            sb.append("  LEFT JOIN ( ");
            sb.append("      select ");

            //按主组织统计
            if(info.getTeamList()!=null&&info.getTeamList().size()>0){
                for(TeamInfoVo item:info.getTeamList()){
                    sb.append(" sum(IF( mi.team_id ='"+item.getTeamId()+"',1,0)) AS team"+item.getTeamId()+", ");
                }
            }
            //按子组织统计
            if(info.getTeamEntryList()!=null&&info.getTeamEntryList().size()>0){
                for(TeamInfoVo item:info.getTeamEntryList()){
                    sb.append(" sum(IF( mi.team_entry_id ='"+item.getTeamId()+"',1,0)) AS team"+item.getTeamId().replace("-","_")+", ");
                }
            }
            //所有组织统计
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("     sum(IF(FIND_IN_SET(mi.team_id,#{info.teamStr})>0 or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0,1,0)) AS count, ");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("     sum(IF(FIND_IN_SET(mi.team_id,#{info.teamStr})>0,1,0)) AS count, ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("     sum(IF(FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0,1,0)) AS count, ");

            }
            sb.append("        date_format(wo.create_time,'%Y-%m-%d') create_time ");
            sb.append("     FROM work_order wo ");
            sb.append("      LEFT JOIN merchant_info mi  ON mi.merchant_no=wo.merchant_no ");

            sb.append("     where 1=1 ");
            sb.append("        and wo.status!='4' and wo.work_product_type='1' ");

            //组织过滤
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("    and ( FIND_IN_SET(mi.team_id,#{info.teamStr})>0  or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 )");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("    and  FIND_IN_SET(mi.team_id,#{info.teamStr})>0  ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("    and FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 ");

            }

            if(info.getCreateTimeBegin()!=null){
                sb.append("   and wo.create_time >= #{info.createTimeBegin} ");
            }
            if(info.getCreateTimeEnd()!=null){
                sb.append("   and wo.create_time <= #{info.createTimeEnd} ");
            }
            sb.append("     GROUP BY date_format(wo.create_time,'%Y-%m-%d') ");
            sb.append("  ) t1 ON t1.create_time = sd.day_date ");

            //t2
            sb.append("  LEFT JOIN ( ");
            sb.append("     select ");
            sb.append("          k1.create_time,count(k1.agent_no) AS count ");
            sb.append("     from  (");
            sb.append("            select DISTINCT ");
            sb.append("                  date_format(wo.create_time,'%Y-%m-%d') create_time, ");
            sb.append("                  ai2.agent_no ");
            sb.append("            FROM work_order wo ");
            sb.append("                     LEFT JOIN merchant_info mi  ON mi.merchant_no=wo.merchant_no ");
            sb.append("                     LEFT JOIN agent_info ai  ON ai.agent_no=mi.agent_no ");
            sb.append("                     LEFT JOIN agent_info ai2  ON ai2.agent_no=ai.one_level_id ");
            sb.append("            where 1=1 ");
            sb.append("               and wo.status!='4' and wo.work_product_type='1' ");

            //组织过滤
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("    and ( FIND_IN_SET(mi.team_id,#{info.teamStr})>0  or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 )");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("    and  FIND_IN_SET(mi.team_id,#{info.teamStr})>0  ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("    and FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 ");

            }
            if(info.getCreateTimeBegin()!=null){
                sb.append("           and wo.create_time >= #{info.createTimeBegin} ");
            }
            if(info.getCreateTimeEnd()!=null){
                sb.append("           and wo.create_time <= #{info.createTimeEnd} ");
            }
            sb.append("         ) k1 ");
            sb.append("        GROUP BY k1.create_time  ");
            sb.append("  ) t2 on t2.create_time = sd.day_date ");


            //t3
            sb.append("  LEFT JOIN ( ");
            sb.append("     select ");
            //所有组织统计
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("     sum(IF(FIND_IN_SET(mi.team_id,#{info.teamStr})>0 or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0,1,0)) AS count, ");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("     sum(IF(FIND_IN_SET(mi.team_id,#{info.teamStr})>0,1,0)) AS count, ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("     sum(IF(FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0,1,0)) AS count, ");

            }
            sb.append("       date_format(wo.create_time,'%Y-%m-%d') create_time ");
            sb.append("     FROM work_order wo ");
            sb.append("      LEFT JOIN merchant_info mi  ON mi.merchant_no=wo.merchant_no ");
            sb.append("     where 1=1 ");
            sb.append("       and wo.status!='4' and wo.work_product_type='1' ");

            //组织过滤
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("    and ( FIND_IN_SET(mi.team_id,#{info.teamStr})>0  or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 )");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("    and  FIND_IN_SET(mi.team_id,#{info.teamStr})>0  ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("    and FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 ");

            }
            if(info.getCreateTimeMonthBegin()!=null){
                sb.append("   and wo.create_time >= #{info.createTimeMonthBegin}");
            }
            if(info.getCreateTimeMonthEnd()!=null){
                sb.append("   and wo.create_time <= #{info.createTimeMonthEnd}");
            }
            sb.append("    GROUP BY date_format(wo.create_time,'%Y-%m-%d') ");
            sb.append("  ) t3 on t3.create_time = date_sub(t1.create_time,interval 1 month) ");


            //主表条件
            sb.append(" where 1=1 ");
            if(StringUtils.isNotBlank(info.getDateStrBegin())){
                sb.append(" and sd.day_date >= #{info.dateStrBegin}");
            }
            if(StringUtils.isNotBlank(info.getDateStrEnd())){
                sb.append(" and sd.day_date <= #{info.dateStrEnd}");
            }
            sb.append(" ORDER BY sd.day_date desc ");
            if(info.getPageFirst()!=null&&info.getPageSize()!=null){
                sb.append(" LIMIT "+info.getPageFirst().toString()+","+info.getPageSize().toString());
            }
            return sb.toString();
        }

        /**
         * 投诉总量统计-汇总
         * @return
         */
        public String countTeamWorkCount(Map<String,Object> param){
            WorkCensusFilter info = (WorkCensusFilter) param.get("info");
            StringBuffer sb=new StringBuffer();
            sb.append("select ");
            sb.append("  count(*) AS count, ");
            sb.append("  sum(t1.count) AS countNum ");

            //主表
            sb.append(" FROM sys_date sd  ");

            //t1
            sb.append("  LEFT JOIN ( ");
            sb.append("      select ");
            //所有组织统计
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("     sum(IF(FIND_IN_SET(mi.team_id,#{info.teamStr})>0 or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0,1,0)) AS count, ");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("     sum(IF(FIND_IN_SET(mi.team_id,#{info.teamStr})>0,1,0)) AS count, ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("     sum(IF(FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0,1,0)) AS count, ");

            }
            sb.append("        date_format(wo.create_time,'%Y-%m-%d') create_time ");
            sb.append("     FROM work_order wo ");
            sb.append("      LEFT JOIN merchant_info mi  ON mi.merchant_no=wo.merchant_no ");
            sb.append("     where 1=1 ");
            sb.append("        and wo.status!='4' and wo.work_product_type='1' ");

            //组织过滤
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("    and ( FIND_IN_SET(mi.team_id,#{info.teamStr})>0  or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 )");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("    and  FIND_IN_SET(mi.team_id,#{info.teamStr})>0  ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("    and FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 ");

            }
            if(info.getCreateTimeBegin()!=null){
                sb.append("   and wo.create_time >= #{info.createTimeBegin}");
            }
            if(info.getCreateTimeEnd()!=null){
                sb.append("   and wo.create_time <= #{info.createTimeEnd}");
            }
            sb.append("     GROUP BY date_format(wo.create_time,'%Y-%m-%d') ");
            sb.append("  ) t1 ON t1.create_time = sd.day_date ");

            //主表条件
            sb.append(" where 1=1 ");
            if(StringUtils.isNotBlank(info.getDateStrBegin())){
                sb.append(" and sd.day_date >= #{info.dateStrBegin}");
            }
            if(StringUtils.isNotBlank(info.getDateStrEnd())){
                sb.append(" and sd.day_date <= #{info.dateStrEnd}");
            }
            return sb.toString();
        }



        /**
         * 下级代理商被投诉排名
         * @param param
         * @return
         */
        public String countAgentWork(Map<String,Object> param) {
            WorkCensusFilter info = (WorkCensusFilter) param.get("info");
            StringBuffer sb=new StringBuffer();
            sb.append("SELECT ");
            sb.append("     create_time,timeGroup,agent_no,agent_name,agent_level,oneAgentNo,oneAgentName,oneAgentSaleName,deal_result2,deal_result3,deal_result4,count,rank ");
            sb.append(" FROM (");
            sb.append("      SELECT *,@rownum:=@rownum+1 AS rownum,IF(@pa=timeGroup,@rank:=@rank+1,@rank:=1) AS rank,@pa:=timeGroup ");
            sb.append("        FROM ( ");
            sb.append("             SELECT ");
            sb.append("                  wo.create_time AS create_time, ");
            sb.append("                  date_format(wo.create_time,'%Y-%m') AS timeGroup, ");
            sb.append("                  ai.agent_no  ,");
            sb.append("                  ai.agent_name  ,");
            sb.append("                  ai.agent_level , ");
            sb.append("                  ai2.agent_no  oneAgentNo,");
            sb.append("                  ai2.agent_name  oneAgentName,");
            sb.append("                  ai2.sale_name oneAgentSaleName , ");
            sb.append("                  sum(IF(wo.deal_result ='2',1,0)) AS deal_result2, ");
            sb.append("                  sum(IF(wo.deal_result ='3',1,0)) AS deal_result3, ");
            sb.append("                  sum(IF(wo.deal_result ='4',1,0)) AS deal_result4, ");
            sb.append("                  count(*) AS count");
            sb.append("             FROM work_order wo ");
            sb.append("                LEFT JOIN merchant_info mi  ON mi.merchant_no=wo.merchant_no ");
            sb.append("                LEFT JOIN agent_info ai  ON ai.agent_no=mi.agent_no ");
            sb.append("                LEFT JOIN agent_info ai2  ON ai2.agent_no=ai.one_level_id ");


            sb.append("            where  1=1");
            sb.append("                and wo.status!='4'  and wo.work_product_type='1' ");

            //组织过滤
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("    and ( FIND_IN_SET(mi.team_id,#{info.teamStr})>0  or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 )");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("    and  FIND_IN_SET(mi.team_id,#{info.teamStr})>0  ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("    and FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 ");

            }
            if(info.getCreateTimeBegin()!=null){
                sb.append("            and wo.create_time >= #{info.createTimeBegin}");
            }
            if(info.getCreateTimeEnd()!=null){
                sb.append("            and wo.create_time <= #{info.createTimeEnd}");
            }
            sb.append("            GROUP BY date_format(wo.create_time,'%Y-%m'),ai.agent_no  ");
            sb.append("            ORDER BY timeGroup desc,count desc,ai.agent_no asc  ");
            sb.append("        ) ff, ");
            sb.append("        (SELECT @rank:=0,@rownum:=0,@pa:=NULL) tt) result ");

            sb.append(" where 1 =1 ");
            if(StringUtil.isNotBlank(info.getAgentNo())){
                sb.append(" and result.agent_no = #{info.agentNo}");
            }
            if(StringUtil.isNotBlank(info.getSaleName())){
                sb.append(" and result.oneAgentSaleName = #{info.saleName}");
            }

            if(info.getPageFirst()!=null&&info.getPageSize()!=null){
                sb.append(" LIMIT "+info.getPageFirst().toString()+","+info.getPageSize().toString());
            }

            return sb.toString();
        }



        /**
         * 下级代理商被投诉排名-汇总
         * @param param
         * @return
         */
        public String countAgentWorkCount(Map<String,Object> param) {
            WorkCensusFilter info = (WorkCensusFilter) param.get("info");
            StringBuffer sb=new StringBuffer();
            sb.append(" SELECT ");
            sb.append(" count(*) AS count, ");
            sb.append(" sum(t1.count) AS countNum ");
            sb.append(" FROM ( ");
            sb.append("   SELECT ");
            sb.append("      date_format(wo.create_time,'%Y-%m') AS timeGroup, ");
            sb.append("      count(*) AS count ");
            sb.append("   FROM work_order wo ");
            sb.append("    LEFT JOIN merchant_info mi  ON mi.merchant_no=wo.merchant_no ");
            sb.append("    LEFT JOIN agent_info ai  ON ai.agent_no=mi.agent_no ");
            sb.append("   where 1=1 ");
            sb.append("     and wo.status!='4' ");
            sb.append("     and wo.work_product_type='1' ");
            if(StringUtil.isNotBlank(info.getAgentNo())){
                sb.append(" and ai.agent_no = #{info.agentNo}");
            }
            if(StringUtil.isNotBlank(info.getSaleName())){
                sb.append(" and ai.sale_name = #{info.saleName}");
            }
            //组织过滤
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("    and ( FIND_IN_SET(mi.team_id,#{info.teamStr})>0  or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 )");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("    and  FIND_IN_SET(mi.team_id,#{info.teamStr})>0  ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("    and FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 ");

            }
            if(info.getCreateTimeBegin()!=null){
                sb.append(" and wo.create_time >= #{info.createTimeBegin}");
            }
            if(info.getCreateTimeEnd()!=null){
                sb.append(" and wo.create_time <= #{info.createTimeEnd}");
            }
            sb.append("     GROUP BY date_format(wo.create_time,'%Y-%m'),ai.agent_no  ");
            sb.append(" ) t1 ");
            return sb.toString();
        }

        /**
         * 一级代理商被投诉排名-汇总
         * @param param
         * @return
         */
        public String countAgentWorkCount2(Map<String,Object> param) {
            WorkCensusFilter info = (WorkCensusFilter) param.get("info");
            StringBuffer sb=new StringBuffer();
            sb.append(" SELECT ");
            sb.append(" count(*) AS count, ");
            sb.append(" sum(t1.count) AS countNum ");
            sb.append(" FROM ( ");
            sb.append("   SELECT ");
            sb.append("      date_format(wo.create_time,'%Y-%m') AS timeGroup, ");
            sb.append("      count(*) AS count ");
            sb.append(" FROM work_order wo ");
            sb.append(" LEFT JOIN merchant_info mi  ON mi.merchant_no=wo.merchant_no ");
            sb.append(" LEFT JOIN agent_info ai  ON ai.agent_no=mi.agent_no ");
            sb.append(" LEFT JOIN agent_info ai2  ON ai2.agent_no=ai.one_level_id ");
            sb.append(" where 1=1 ");
            sb.append("     and wo.status!='4' and wo.work_product_type='1' ");
            if(StringUtil.isNotBlank(info.getAgentNo())){
                sb.append(" and ai2.agent_no = #{info.agentNo}");
            }
            if(StringUtil.isNotBlank(info.getSaleName())){
                sb.append(" and ai2.sale_name = #{info.saleName}");
            }

            //组织过滤
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("    and ( FIND_IN_SET(mi.team_id,#{info.teamStr})>0  or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 )");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("    and  FIND_IN_SET(mi.team_id,#{info.teamStr})>0  ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("    and FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 ");

            }
            if(info.getCreateTimeBegin()!=null){
                sb.append(" and wo.create_time >= #{info.createTimeBegin}");
            }
            if(info.getCreateTimeEnd()!=null){
                sb.append(" and wo.create_time <= #{info.createTimeEnd}");
            }
            sb.append("     GROUP BY date_format(wo.create_time,'%Y-%m'),ai2.agent_no  ");
            sb.append(" ) t1 ");
            return sb.toString();
        }

        /**
         * 一级代理商被投诉排名
         * @param param
         * @return
         */
        public String countAgentWork2(Map<String,Object> param) {
            WorkCensusFilter info = (WorkCensusFilter) param.get("info");
            StringBuffer sb=new StringBuffer();
            sb.append("SELECT ");
            sb.append("     create_time,timeGroup,oneAgentNo,oneAgentName,oneAgentSaleName,deal_result2,deal_result3,deal_result4,count,rank,countPart ");
            sb.append(" FROM (");
            sb.append("      SELECT *,@rownum:=@rownum+1 AS rownum,IF(@pa=timeGroup,@rank:=@rank+1,@rank:=1) AS rank,@pa:=timeGroup ");
            sb.append("        FROM ( ");

            sb.append(" select t1.*," +
                    "   CONCAT(round(t1.count / t2.count*100,2),'%')  AS countPart" +
                    "   FROM ( ");
            sb.append("             SELECT ");
            sb.append("                  wo.create_time AS create_time, ");
            sb.append("                  date_format(wo.create_time,'%Y-%m') AS timeGroup, ");
            sb.append("                  ai2.agent_no oneAgentNo, ");
            sb.append("                  ai2.agent_name oneAgentName, ai2.sale_name oneAgentSaleName,");
            sb.append("                  sum(IF(wo.deal_result ='2',1,0)) AS deal_result2, ");
            sb.append("                  sum(IF(wo.deal_result ='3',1,0)) AS deal_result3, ");
            sb.append("                  sum(IF(wo.deal_result ='4',1,0)) AS deal_result4, ");
            sb.append("                  count(*) AS count");
            sb.append("             FROM work_order wo ");
            sb.append("                LEFT JOIN merchant_info mi  ON mi.merchant_no=wo.merchant_no ");
            sb.append("                LEFT JOIN agent_info ai  ON ai.agent_no=mi.agent_no ");
            sb.append("                LEFT JOIN agent_info ai2  ON ai2.agent_no=ai.one_level_id ");

            sb.append("            where  1=1");
            sb.append("                and wo.status!='4' and wo.work_product_type='1' ");
            //组织过滤
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("    and ( FIND_IN_SET(mi.team_id,#{info.teamStr})>0  or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 )");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("    and  FIND_IN_SET(mi.team_id,#{info.teamStr})>0  ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("    and FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 ");

            }
            if(info.getCreateTimeBegin()!=null){
                sb.append("            and wo.create_time >= #{info.createTimeBegin}");
            }
            if(info.getCreateTimeEnd()!=null){
                sb.append("            and wo.create_time <= #{info.createTimeEnd}");
            }
            sb.append("            GROUP BY date_format(wo.create_time,'%Y-%m'),ai2.agent_no  ");
            sb.append("            ORDER BY timeGroup desc,count desc, ai2.agent_no ");
            sb.append(" ) t1 ");

            sb.append(" left join ( ");

            sb.append("             SELECT ");
            sb.append("                  date_format(wo.create_time,'%Y-%m') AS timeGroup, ");
            sb.append("                  count(*) AS count");
            sb.append("             FROM work_order wo ");
            sb.append("                LEFT JOIN merchant_info mi  ON mi.merchant_no=wo.merchant_no ");
            sb.append("                LEFT JOIN agent_info ai  ON ai.agent_no=mi.agent_no ");
            sb.append("                LEFT JOIN agent_info ai2  ON ai2.agent_no=ai.one_level_id ");
            sb.append("            where  1=1");
            sb.append("                and wo.status!='4' and wo.work_product_type='1' ");

            //组织过滤
            if(StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){ //1 2 都 true
                sb.append("    and ( FIND_IN_SET(mi.team_id,#{info.teamStr})>0  or FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 )");

            }else if(StringUtils.isNotBlank(info.getTeamStr())&&!StringUtils.isNotBlank(info.getTeamEntryStr())){//1 true 2 false
                sb.append("    and  FIND_IN_SET(mi.team_id,#{info.teamStr})>0  ");

            }else if(!StringUtils.isNotBlank(info.getTeamStr())&&StringUtils.isNotBlank(info.getTeamEntryStr())){//1 false 2 true
                sb.append("    and FIND_IN_SET(mi.team_entry_id,#{info.teamEntryStr})>0 ");

            }
            if(info.getCreateTimeBegin()!=null){
                sb.append("            and wo.create_time >= #{info.createTimeBegin}");
            }
            if(info.getCreateTimeEnd()!=null){
                sb.append("            and wo.create_time <= #{info.createTimeEnd}");
            }
            sb.append("            GROUP BY date_format(wo.create_time,'%Y-%m') ");
            sb.append("  ) t2 on t2.timeGroup = t1.timeGroup ");

            sb.append("        ) ff, ");
            sb.append("        (SELECT @rank:=0,@rownum:=0,@pa:=NULL) tt) result ");
            sb.append(" where 1 =1 ");
            if(StringUtil.isNotBlank(info.getAgentNo())){
                sb.append(" and result.oneAgentNo = #{info.agentNo}");
            }
            if(StringUtil.isNotBlank(info.getSaleName())){
                sb.append(" and result.oneAgentSaleName = #{info.saleName}");
            }
            if(info.getPageFirst()!=null&&info.getPageSize()!=null){
                sb.append(" LIMIT "+info.getPageFirst().toString()+","+info.getPageSize().toString());
            }




            return sb.toString();
        }

    }



}
